#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
Mikan データベース RDB 機能

Mikan Python Web Framework
"""

__author__ = "K.Sonohara"
__status__ = "develop"
__version__ = "0.0.0_36"
__date__    = "2014/01/05"
__license__ = 'LGPL'

# ------------------------------------------------------------
## インポート
# ------------------------------------------------------------
# Python
import traceback
import datetime

# ESiPC
from esipc.resource.iniresource import ESiPCINIFileResourceBundle

# Mikan
from mikan.db import AbstractMikanDB
from mikan.db import AbstractMikanItemDB

# ------------------------------------------------------------
## Variable Section
# ------------------------------------------------------------
## Resource Variable
RESOURCE = ESiPCINIFileResourceBundle(__file__)

# ------------------------------------------------------------
## Function Section
# ------------------------------------------------------------

# ------------------------------------------------------------
## Get resource for Multi Locale
# @param key Resource key
# @param default Default value
# @param locale Locale
# @param resource Target Resource bundle
def _(key, default, locale=None, resource=RESOURCE):
	try:
		return resource.getString(key, default, locale)
	except:
		return default

# ------------------------------------------------------------
## クラス
# ------------------------------------------------------------

# ------------------------------------------------------------
## Mikan RDB DB抽象 クラス
#
class AbstractMikanRDBDB(AbstractMikanDB):

	def getCursor(self):
		if (self.FCursor == None):
			self.FCursor = self.FConnector.cursor()
		return self.FCursor

	def __init__(self,  request):
		super(AbstractMikanRDBDB, self).__init__( request)

		self.FCursor = None

	def setup(self, config, section):
		pass

	def setConnector(self, value):
		self.FConnector = value
		self.FConnector.row_factory = MikanItemFactory

	def close(self):
		try:
			if (self.getCursor() != None):
				self.FCursor.close()
		except:
			pass
		self.FCursor   = None
		super(AbstractMikanRDBDB, self).close()

	def closeCursor(self):
		if (self.FCursor != None):
			self.FCursor.close()
		self.FCursor == None

	def commit(self):
		self.getConnector().commit()

	def rollback(self):
		self.getConnector().rollback()

	def execute(self, sql, params=None):
		# ログ
		if self.islogdebug:
			self.logger.debug(sql)
			self.logger.debug(params)
#		print sql
#		print params
		if params == None:
			self.getCursor().execute(sql)
		else:
			self.getCursor().execute(sql, params)

# ------------------------------------------------------------
## Mikan DB抽象 クラス
#
class AbstractMikanRDBItemDB(AbstractMikanRDBDB, AbstractMikanItemDB):

	def __init__(self):
		super(AbstractMikanItemDB, self).__init__()

	def dbDropSQL(self, entity):
		return u"DROP TABLE %s;" % entity.definename

	def dbDrop(self, entity):
		self.execute(self.dbDropSQL(entity))

	def dbCreateSQL(self, entity):
		sa = ""

		try:
			id = entity.getIDAttribute()
			sa = self.dbCreateSQLs(entity, id)
		except:
			pass

		for a in entity.attributes.values():
			if (a.isdynamic == False):
				if (a.definename != "id"):
					sa = sa + self.dbCreateSQLs(entity, a)

		return u"CREATE TABLE %s(%s PRIMARY KEY (%s));" % (entity.definename, sa, "id")

	def dbCreateSQLs(self, entity, attribute):
		notnull = ""
		if (attribute.isrequired):
				notnull = " NOT NULL"
		return u" %s %s%s," % (attribute.definename, self.sqlType(attribute), notnull)

	def dbCreate(self, entity):
		self.execute(self.dbCreateSQL(entity))

	def sqlType(self, attribute):
		if (attribute.getClassType() == "string"):
			return self.sqlTypeString(attribute)
		if (attribute.getClassType() == "boolean"):
			return self.sqlTypeShort(attribute)
		if (attribute.getClassType() == "short"):
			return self.sqlTypeShort(attribute)
		if (attribute.getClassType() == "int"):
			return self.sqlTypeInt(attribute)
		if (attribute.getClassType() == "long"):
			return self.sqlTypeLong(attribute)
		if (attribute.getClassType() == "timestamp"):
			return self.sqlTypeTimeStamp(attribute)

		return None

	def sqlTypeString(self, attribute):
#		return "TEXT"
		return u"VARCHAR(%d)" % attribute.length

	def sqlTypeShort(self, attribute):
		return "INTEGER"
#		return u"INT(%d)" % 16

	def sqlTypeInt(self, attribute):
		return "INTEGER"
#		return u"INT(%d)" % 32

	def sqlTypeLong(self, attribute):
		return "INTEGER"
#		return u"INT(%d)" % 64

	def sqlTypeTimeStamp(self, attribute):
		return "TIMESTAMP"


	def createSQLID(self, entity, attribute):
		return True

	def createSQL(self, entity):
		aa = ""
		va = ""

		try:
			id = entity.getIDAttribute()
			if self.createSQLID(entity, id):
				aa = self.createSQLAttributes(entity, id)
				va = "NULL"
		except:
			pass

		for a in entity.attributes.values():
			if (a.isdynamic == False):
				if a.definename != "id":
					try:
						v = "?"
						if (aa != ""):
							aa = aa +","
							va = va +","
						aa = aa + self.createSQLAttributes(entity, a)
						va = va + v
					except:
						pass

		return u"INSERT INTO %s(%s) values(%s);" % (entity.definename, aa, va)

	def createSQLAttributes(self, entity, attribute):
		return u" %s" % attribute.definename

	def findFor(self, list, index):
		for i in list:
			if i == index:
				return True
		return False

	def paramFrom(self, entity, filter):
		params = ()
		for a in entity.attributes.values():
			if (a.isdynamic != True):
				if not self.findFor(filter, a.definename):
					params = params + (self.valueSQL(entity, a),)

		return params

	def create(self, entity, revision=None):
		if (entity.isDateIDEntity()):
			entity["createdate"] = datetime.datetime.now()
			entity["lastupdate"] = datetime.datetime.now()

		if entity.issequenceentity:
			if sequence:
				entity.setSequenceValue(self.get_sequence_value_next(entity, entity.getSequenceValue()))
				entity.setRootIDValue(entity.getIDValue())
				entity.setEffectiveSequenceValue(True)
				entity.setLastSequenceValue(True)
			else:
				master = entity.getMasterClass()({})
				self.create(master)

				entity.setSequenceValue(self.get_sequence_value_first(entity))
				entity.setRootIDValue(entity.getIDValue())
				entity.setMasterIDValue(master.getIDValue())
				entity.setEffectiveSequenceValue(True)
				entity.setLastSequenceValue(True)

		params = self.paramFrom(entity, ["id"])
		self.execute(self.createSQL(entity), params)

		entity["id"] = self.lastID()
		# ログ
		if self.islogdebug:
			self.logger.debug(entity["id"])

	def updateSQL(self, entity):
		aa = ""
		for a in entity.attributes.values():
			if (a.isdynamic == False):
				if a.definename != "id":
					if a.definename != "createdate":
						try:
							v = "%s = ?" % (a.definename)
							if (aa != ""):
								aa = aa +","
							aa = aa + v
						except:
							pass

		return u"UPDATE %s SET %s WHERE id = '%s';" % (entity.definename, aa, entity["id"])

	def valueSQL(self, entity, attribute):
		if (entity[attribute.definename] == None):
#			return "NULL"
			return None
		if (entity[attribute.definename] == ""):
#			return "NULL"
			return None

		try:
			if (entity[attribute.definename] == True):
				return 1
			if (entity[attribute.definename] == False):
				return 0
		except:
			pass

		if (attribute.getClassType() == "int"):
			return entity[attribute.definename]
		if (attribute.getClassType() == "long"):
			return entity[attribute.definename]
		if (attribute.getClassType() == "date"):
			return entity[attribute.definename]
#		if (attribute.getClassType() == "string"):
		if (attribute.getClassType() == "timestamp"):
			return entity[attribute.definename]

		return unicode(entity[attribute.definename])

	def updateSQLAttributes(self, entity, attribute):
		return u" %s" % attribute.definename

	def update(self, entity):
		try:
			entity.getLastUpDateAttribute()
			entity["lastupdate"] = datetime.datetime.now()
		except:
			pass

		params = self.paramFrom(entity, ["id", "lastupdate"])
		self.execute(self.updateSQL(entity), params)

	def removeSQL(self, entity):
		return u"DELETE FROM %s WHERE id = '%s';" % (entity.definename, entity["id"])

	def remove(self, entity):
		self.execute(self.removeSQL(entity))

	def getSQL(self, entityclass, id):
		e = entityclass({})
		return u"SELECT * FROM %s WHERE id = '%s';" % (e.definename, id)

	def get(self, entityclass, id):
		self.execute(self.getSQL(entityclass, id))
		for row in self.getCursor():
			return self.entityFrom(entityclass, row)
		return None

	def getsSQL(self, entityclass, wheres, orderby):
		e = entityclass({})
		sql = u"SELECT * FROM %s" % (e.definename)

		if (wheres):
			sql = u"%s WHERE %s" % (sql, wheres)
		if (orderby):
			sql = u"%s ORDER BY %s" % (sql, orderby)
		return sql + ";";

	def querySQL(self, entity):
		self.logger.debug(entity)
		aa = ""
		for a in entity.attributes.values():
			if (a.isdynamic == False):
				try:
					self.logger.debug(a)
					value = entity[a.definename]
					self.logger.debug(value)
					if (value != None) and (value != ""):
						v = None
						try:
							if value.find("*") != -1:
								v = u"%s LIKE '%s'" % (a.itemname, value.replace("*", "%"))
						except:
							pass

						if v == None:
								v = u"%s = '%s'" % (a.itemname, value)
						if (aa != ""):
							aa = aa +" AND "
						aa = aa + v
				except:
					self.logger.debug(traceback.format_exc())

		if aa != "":
			where = "WHERE " + aa
		else:
			where = ""

		# TODO
		return u"SELECT * FROM %s %s;" % (entity.definename, where)

	def gets(self, entityclass, wheres=None, orderby=None):
		self.execute(self.getsSQL(entityclass, wheres, orderby))

		l = []
		for row in self.getCursor():
			l.append(self.entityFrom(entityclass, row))

		return l

	def query(self, entityclass, entity):
		self.logger.debug(entity)
		self.execute(self.querySQL(entity))

		l = []
		for row in self.getCursor():
			l.append(self.entityFrom(entityclass, row))

		return l

	def entityFrom(self, entityclass, row):
		try:
			return entityclass(row)
		except:
			self.logger.error(traceback.format_exc())
		return None

	def sqlLastID(self):
		return u""

	def lastID(self):
		self.execute(self.sqlLastID())
		for row in self.getCursor():
			return row["id"]
		return None


# ------------------------------------------------------------
## Mikan DB アイテム クラス
#
class MikanDBItem(dict):
	__getattr__ = dict.__getitem__
	__setattr__ = dict.__setitem__
	__delattr__ = dict.__delitem__


# ------------------------------------------------------------
## Mikan DB アイテム クラス
#
def MikanItemFactory(cursor, row):
	d = {}

	for idx, col in enumerate(cursor. description):
		s = row[idx]

#        d[idx] = s
		d[col[0].lower()] = s

	return MikanDBItem(d)

# ------------------------------------------------------------
## メイン関数
# ------------------------------------------------------------
if __name__ == '__main__':
	pass

# ------------------------------------------------------------
